package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import base.DbConnect;
import model.Department;

/**
 * 部门类别Dao类
 * 
 * @author Su
 *
 */
public class DepartmentDao {
	public static int add(Department department) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		String sql = "insert into department values (?,?,?)";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, department.getD_id());
			stmt.setString(2, department.getD_name());
			stmt.setString(3, department.getRemarks());
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}

	public static ArrayList<Department> select() {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Department> arrayList = new ArrayList<Department>();
		
		String sql;
		sql = "SELECT * FROM department";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Department department = new Department();
				department.setD_id(rs.getString("d_id"));
				department.setD_name(rs.getString("d_name"));
				department.setRemarks(rs.getString("remarks"));
				arrayList.add(department);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arrayList;
	}
	
	public static int find() {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		int res = 0;
		String sql;
		sql = "SELECT * FROM department";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				res++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}
	
	public static Department select(String d_id) {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		Department department = new Department();
		String sql;
		sql = "SELECT * FROM department where d_id = '" + d_id + "'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				department.setD_id(rs.getString("d_id"));
				department.setD_name(rs.getString("d_name"));
				department.setRemarks(rs.getString("remarks"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return department;
	}

	/**
	 * 删除信息
	 * @param department
	 * @return
	 */
	public static int delete(String d_id) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		String sql = "delete from department where d_id = ?";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, d_id);
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}
	
	/**
	 * 更新信息
	 * @param department
	 * @return
	 */
	public static int updata(Department department) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		try {
			String sql = "update department set d_name = ? ,remarks = ? where d_id = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, department.getD_name());
			stmt.setString(2, department.getRemarks());
			stmt.setString(3, department.getD_id());
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}
	
}
